package cn.com.yusys.yusp.custManager.service;

import cn.com.yusys.yusp.admin.domain.RoleLevelProperties;
import cn.com.yusys.yusp.commons.mapper.QueryModel;
import cn.com.yusys.yusp.custManager.domain.OcrmFCiFqDbcol;
import cn.com.yusys.yusp.custManager.repository.mapper.FlexibleQueryConMapper;
import cn.com.yusys.yusp.custManager.repository.mapper.FlexibleQueryMapper;
import cn.com.yusys.yusp.custManager.repository.mapper.MarketCustGroupMapper;
import cn.com.yusys.yusp.custManager.utils.FlexibleQueryUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zoie
 * @Description: 灵活查询
 * @Date: 2021/5/1
 * @Version: 1.0
 */

@Service
public class FlexibleQueryService {
    // 导出最大条数
    private static int MAX_LENGTH = 500000;

    @Autowired
    private FlexibleQueryMapper mapper;

    @Autowired
    private FlexibleQueryConMapper flexibleQueryConMapper;

    @Autowired
    private MarketCustGroupMapper marketCustGroupMapper;

    private final Logger logger = LoggerFactory.getLogger(FlexibleQueryService.class);

    /**
     * 客户灵活查询查询左侧树
     * @return list
     */
    public List<Map<String, Object>> getIndexTree() {
        List<Map<String, Object>> list = mapper.getIndexTree();
        return list;
    }

    /**
     * 灵活查询查询字段属性值类型 文本框..
     *
     * @param model
     * @return list
     */
    public List<Map<String, Object>> showColType(QueryModel model) {
        return mapper.showColType(model);
    }

    /**
     * 客户灵活查询查询结果
     * @param model
     * @param paging
     * @return
     */
    public List<Map<String, Object>> queryResult(QueryModel model, boolean paging) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            String dataMonth = (String) model.getCondition().get("dataMonth"); // 客户业务数据月份
            dataMonth = dataMonth.substring(0, 10).replaceAll("-", "");
            List conditionAttrs = (List) model.getCondition().get("conditionAttrs");// 条件参数
            List results = (List) model.getCondition().get("results");// 结果列参数
            String roleCode = model.getCondition().get("roleId").toString();
            String orgId = model.getCondition().get("orgId").toString(); // 当前用户所属机构
            String userId = model.getCondition().get("userId").toString(); // 当前用户名称
            String mgrFlag = flexibleQueryConMapper.getUuid(roleCode).get("roleLevel");// 1 总行；2 分行管理员；3 支行行长 4 副行长；5 经理
            String topNum = "";
            if (model.getCondition().containsKey("topNum")) {
                topNum = String.valueOf(model.getCondition().get("topNum"));
            }
            String isexport = "";
            if (model.getCondition().containsKey("isexport")) {
                isexport = String.valueOf(model.getCondition().get("isexport"));
            }


            JSONArray jaCondition = JSONArray.parseArray(JSON.toJSONString(conditionAttrs));// 查询条件
            JSONArray jaColumns = null; // 查询结果字段
            if (results != null && results.size() != 0) {
                jaColumns = JSONArray.parseArray(JSON.toJSONString(results));
            }
            // sql拼装处理
            Map<String, Object> res = generatorSql(jaCondition, jaColumns,
                    isexport, mgrFlag, orgId, dataMonth, model.getCondition().get("page"),roleCode,userId);
            // 获得拼装的sql语句，可以添加额外控制语句
            String sql = (String) res.get("SQL");
            logger.info("拼装后的sql" + sql);
            String ordersql = (String) res.get("ORDERSQL");
            logger.info("topNum:" + topNum);
            logger.info("ordersql:" + ordersql);

            model.getCondition().put("topNum", topNum);
            model.getCondition().put("SQL", sql);
            model.getCondition().put("ORDERSQL", ordersql);
            if (paging && model.getCondition().get("page") != null) {
                PageHelper.startPage((int) model.getCondition().get("page"), (int) model.getCondition().get("size"));
            }
            list = mapper.queryResult(model);
            if (paging && model.getCondition().get("page") != null) {
                PageHelper.clearPage();
            }
            if (list.size() > 0) {
                for (int i = 0; i < list.size(); i++) {
                    list.get(0).put("sql", sql + ordersql);
                }
            }
        } catch (BadSqlGrammarException e) {
            e.printStackTrace();
            return null;
        }
        return list;
    }

    // 拼接sql
    public Map<String, Object> generatorSql(JSONArray jaCondition, JSONArray jaColumns, String isexport, String mgrFlag,
                                            String orgId, String dataMonth, Object page,String roleCode,String userId) {
        String conditionColumns = ""; // 条件列字段编号
        for (Object oa : jaCondition) {
            JSONArray joa = JSONArray.parseArray(JSON.toJSONString(oa));
            for (Object o : joa) {
                JSONObject jo = (JSONObject) o;
                if ("".equals(conditionColumns)) {
                    conditionColumns += jo.getLong("SS_COL_ITEM");
                } else {
                    conditionColumns += "," + jo.getLong("SS_COL_ITEM");
                }
            }
        }
        // 查询条件列字段信息
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT cols.* FROM OCRM_F_CI_FQ_DBCOL cols WHERE cols.id in (" + conditionColumns + ")");
        List<OcrmFCiFqDbcol> conditionColumnsObj = mapper.getResultList(sb.toString());

        /**
         * 查询结果列对象
         */
        String resultColumnsId = "0";
        if (jaColumns != null) {
            for (Object o : jaColumns) {
                JSONObject jo = (JSONObject) o;
                resultColumnsId += "," + jo.getLong("columnId");
            }
        }
        StringBuilder sbsql = new StringBuilder();
        sbsql.append("SELECT cols.* FROM OCRM_F_CI_FQ_DBCOL cols WHERE cols.id in (" + resultColumnsId + ")");
        List<OcrmFCiFqDbcol> dscs = mapper.queryColumns(sbsql.toString());

        // SELECT语句
        String selectSQL = "";
        if ("1".equals(isexport)) {// 如果是导出，则数据不做脱敏处理
            selectSQL = FlexibleQueryUtils.createSelectColumnsexport(jaCondition, dscs, jaColumns, page);
        } else {
            selectSQL = FlexibleQueryUtils.createSelectColumns(dscs, jaColumns);
        }
        logger.info("selectSQL==" + selectSQL);

        // 条件语句
        String whereSQL = FlexibleQueryUtils.createWhereColumns(jaCondition, conditionColumnsObj, mgrFlag, orgId);
//        whereSQL += " AND ETL_DT = current_date -1 ";
//        logger.info("whereSQL==" + whereSQL);


        //----------------by zoie---查询网格等级--------------
        String gridSql = "";
        if(RoleLevelProperties.ROLELEVEL2.contains(roleCode)){
            String gridIdLevel1 = marketCustGroupMapper.getGridIdByOrgId(orgId);
            gridSql = " AND base_info.GRID_ID="+gridIdLevel1;
        }else if(RoleLevelProperties.ROLELEVEL3.contains(roleCode)){
            gridSql = " AND base_info.USER_NO="+userId;
        }

        // 排序语句
        String orderSQL = "";
        if (jaColumns != null) {
            orderSQL = FlexibleQueryUtils.createOrderSQL(dscs, jaColumns);
        }
        Map<String, Object> lastResult = new HashMap<String, Object>();

        String finalSql = "SELECT " + selectSQL + " FROM  WCRM_FLEX_QUERY_INFO base_info WHERE 1=1 ";
        finalSql += gridSql;
        finalSql += " AND (" + whereSQL+")";
        String ordersql = "";
        if (!orderSQL.equals("")) {
            ordersql += " ORDER BY " + orderSQL;
        }
        logger.info("finalSql++++" + finalSql);
        logger.info("ORDERSQL++++" + ordersql);
        lastResult.put("SQL", finalSql);
        lastResult.put("ORDERSQL", ordersql);
//        lastResult.put("lookupColumns", look);
        return lastResult;
    }
}